<?php
namespace App\Core;

use mysqli;

/**
 * Created by PhpStorm.
 * User: 何婷婷
 * Date: 2016/11/8
 * Time: 12:47
 *
 *  DB类使用规则：
 *   1.创建DB对象即实例换一个mysqli对象并连接成功。
 *   2.调用setTable设置表名，并返回当前DB对象。
 *   3.调用setWhere设置Where条件，并返回当前DB对象。
 *   4.调用setOrderBy设置排序规则，并返回当前DB对象。
 *   5.调用setLimit设置Limit和offset，并返回当前DB对象。
 *   6.调用setJoin和setIn设置Join on，并返回当前DB对象。
 *   7.调用query,select，insert，update，del，getById，find等执行查询语言,并以数组或lastId返回执行结果。
 *
 */
class DB
{
    private $host;
    private $name;
    private $psd;
    private $base;
    private $con = '';//数据库连接资源
    private $resultDb = '';  //结果集
    private $msg = '';//返回错误信息
    private $rowsNum = ''; //返回结果数
    private $rowsRst = '';//单条记录数字字
    private $rowsArray = array();//返回结果数组
    private $table = 'User';
    private $where = ''; //where条件
    private $orderBy = ''; //orderBy排序条件
    private $limit = '';
    private $offset = '';
    private $join = '';  //join — 别名 implode() — 将一个一维数组的值转化为字符串
    private $id;  //返回id

    public function __construct()
    {
        $config = @include_once(CONFIG_PATH . '/database.php');
        $this->host = $config['host'];
        $this->name = $config['name'];
        $this->psd = $config['psd'];
        $this->base = $config['database'];
        $this->con_init();
    }

    //连接数据库
    public function con_init()
    {
        $this->con = new \mysqli($this->host, $this->name, $this->psd, $this->base);//@屏蔽错误信息
        if ($this->con->connect_error) {

            die('Connect Error (' . $this->con->connect_errno . ') '
                . $this->con->connect_error);

        }else{
            echo '连接数据库成功';
        }
    }

    //查询数据库连接
    public function query_result()
    {
        if ($this->con == '') {
            $this->con_init();
        }
        return $this->resultDb = $this->con->query($this->con);//mysqli::query -- mysqli_query — 对数据库执行一次查询
    }

    /**
     * @return mixed 这是一个混合类型
     *
     */
    public function getTable()
    {
        return $this->table;
    }

    /**
     * @param mixed $table
     *
     * 调用setTable设置表名，并返回当前DB对象。
     *
     * @return mixed $this
     */
    public function setTable($table)
    {
        $this->table = $table;
        return $this;
    }

    /**
     * @param mixed $data
     *
     * 调用setWhere设置Where条件，并返回当前DB对象。
     *
     * @return string $this
     */
    public function setWhere($data)
    {
        $this->where = $this->parseWhereSql($data);
        return $this;
    }

    /**
     * @param mixed $data
     *
     * 调用setOrderBy设置排序规则，并返回当前DB对象。
     *
     * @return string $this
     */
    public function setOrderBy($data)
    {

        $this->orderBy = $this->parseOrderSql($data);
        return $this;
    }

    /**
     * @param array $data
     *
     * 调用setLimit设置Limit和offset，并返回当前DB对象.
     *
     * @return string $this
     */
    public function setLimit($data)
    {
        $this->limit .= "limit " . $data . " ";
        return $this;
    }

    public function setOffset($data)
    {
        $this->offset = "OFFSET " . $data . " ";
        return $this;
    }

    /**
     * @param array $join
     * @param  array $data
     *
     * //调用setJoin 设置Join ，并返回当前DB对象。
     *
     * @return string $this
     */
    public function setJoinOn($join, $data)
    {

        $tables = explode(",", $this->table);
        if (count($tables) < 2) {
            return $this;
        }
        $this->table = $tables[0];
        $this->join = $join . " " . $tables[1] . 'on ' . $data . " ";

        return $this;
    }

    /**
     * 解析insert
     *
     * @param  array $data
     *
     * 数据传进来之后通过SQL语句解析
     *
     * @return  string $sql
     *
     */
    private function parseInsertSql($data)
    {
        $key = array_keys($data);  //array_keys — 返回数组中所有的键
        $value = array_values($data);//array_values — 返回数组中所有的值
        $keys = '';
        $values = '';
        for ($i = 0; $i < count($key); $i++) {

            $keys .= "`" . $key[$i] . "`";
            if ($i != count($key) - 1)
                $keys .= ",";
        }
        for ($i = 0; $i < count($value); $i++) {
            if (is_numeric($value[$i])) {  //is_numeric — 检测变量是否为数字或数字字符串
                $values .= $value[$i];
            } else {
                $values .= "'" . $value[$i] . "'";
            }
            if ($i != count($value) - 1)
                $values .= ",";
        }
        $table = $this->table;
        return "insert into $table ( $keys ) VALUES ( $values )";
    }

    //insert插入数据
    public function insert($data)
    {
        $sql = $this->parseInsertSql($data);
        var_dump($sql);
        $result = $this->con->query($sql);
        if ($result) {
            return $this->con->insert_id;  //返回最后一个查询中使用自动生成的id
        } else
            return false;
    }


    /**
     * select 解析
     *
     * @param   string $filed
     *
     * @return  string
     */
    private function parseSelectSql($filed = '*')
    {
        echo $filed,"<br>";
        $table = $this->table;
        $sql = '';
        if (!is_array($filed)) {
            $sql .= "select $filed from $table ";
        } else {
            var_dump($filed);
            echo "<br>";
            $values = '';
            for ($i = 0; $i < count($filed); $i++) {
                $values .= " `" . $filed[$i] . "` ";
                if ($i < count($filed) - 1)
                    $values .= ", ";
                $sql .= "select $values from $table ";
            }
        }

        if ($this->join !== '')
            $sql .= $this->join;
        if ($this->where !== '')
            $sql .= $this->where;
        if ($this->orderBy !== '')
            $sql .= $this->orderBy;
        if ($this->limit !== '')
            $sql .= $this->limit;
        if (!$this->offset == '')
            $sql .= $this->offset;
        return $sql;

    }


    //  调用select语句
    public function select($data = '*')
    {

        $sql = $this->parseSelectSql($data);

        $result = $this->con->query($sql);

        if ($result)
            return $result->fetch_assoc();//获取一个结果行作为一个关联数组中
        else
            return false;
    }


    /**
     * update 解析
     *
     * @param   array $data
     *
     * 数据传进来之后通过SQL语句解析
     *
     * @return  string $sql
     */
    private function parseUpdateSql($data)
    {
        $keys = '';
        $i = 1;
        foreach ($data as $k => $v) {
            if (is_numeric($v)) {
                $keys .= "`" . $k . "`" . '=' . $v;
            } else {
                $keys .= "`" . $k . "`" . '=' . "'" . $v . "'";
            }

            if ($i++ < count($data))
                $keys .= ', ';
        }

        $table = $this->table;
        return "update $table set $keys ";
    }

    public function update($data, $where)
    {
        $sql = $this->parseUpdateSql($data) . $this->parseWhereSql($where);
        $result = $this->con->query($sql);
        return $result;
    }


    /**
     *
     * @return string
     */
    private function parseDeleteSql()
    {

        $table = $this->table;
        return "delete from $table  ";
    }

    //调用delete语句
    public function delete($where)
    {
        $sql = $this->parseDeleteSql() . $this->parseWhereSql($where);
        return $this->con->query($sql);  //失败时返回 FALSE

    }

    /**
     * @param array $data
     *
     *  where条件进行封装
     *
     * @return string $where
     */
    private function parseWhereSql($data)
    {
        if (empty($data))
            return '';
        $ar = array();
        foreach ($data as $key => $value) {
            if (!is_array($value)) {
                if (is_numeric($value)) {
                    $ar[] = $key . "=" . $value;
                } else {
                    $ar[] = $key . "=" . "'" . $value . "'";
                }
            } else {
                if ((($value[0] == 'in') || ($value[0] == 'not in')) && is_array($value[1]) && !empty($value[1])) {
                    $tmp = $key . " in ( ";
                    for ($i = 0; $i < count($value[1]); $i++) {
                        if (is_numeric($value[1][0])) {
                            $tmp .= $value[1][$i];
                        } else
                            $tmp .= "'" . $value[1][$i] . "'";
                        if ($i < (count($value[1]) - 1))
                            $tmp .= ',';
                    }
                    $tmp .= " )";
                    $ar[] = $tmp;
                    continue;//终止本次循环，继续执行下次循环
                }

                if ($value[0] == 'between' && is_array($value[1]) && !empty($value[1])) {
                    $bet = $key . " between ( ";
                    if (is_numeric($value[1][0])) {
                        $bet .= $value[1][0] . " and " . $value[1][1];
                    } else {
                        $bet .= "'" . $value[1][0] . "'" . " and " . "'" . $value[1][1] . "'";
                    }
                    $bet .= " ) ";
                    $ar[] = $bet;
                    continue;
                }

                if (is_numeric($value[1])) {
                    $ar[] = $key . " " . $value[0] . " " . $value[1];
                } else {
                    $ar[] = $key . " " . $value[0] . " '" . $value[1] . "'";
                }
            }
        }
        $where = ' where ';
        $i = 1;
        foreach ($ar as $v) {
            $where .= $v;
            if ($i++ < count($ar)) {
                $where .= " and ";
            }
        }
        $this->where = $where;
        return $where;
    }


    /**
     *  order by 解析
     * @param array $data
     *
     * @return  string $order
     */
    private function parseOrderSql($data)
    {
        $key = array_keys($data);
        $k = '';
        for ($i = 0; $i < count($key); $i++) {
            if (!is_numeric($key)) {
                $k .= "'" . $key[$i] . "'";
            } else {
                $k .= $key[$i];
            }
            if ($i < count($key) - 1) {
                $k .= ",";
            }
        }

        $order = ' order by ' . $k;
        $this->orderBy = $order;
        return $order;

    }

    //查询结果，返回记录数
    public function getRowsNum()
    {
        $this->query_result();
        if (mysqli_connect_errno() == '') {
            return $this->con->fetch_row($this->resultDb);//mysql_num_rows — 取得结果集中行的数目
        } else {
            return '';
        }
    }

    //将查询结果输出成一个数组并返回，单条记录
    public function getRowsRst()
    {
        $this->query_result();
        if (mysqli_connect_errno() == '') {
            return $this->rowsRst = $this->con->fetch_row($this->resultDb);//mysql_fetch_array — 从结果集中取得一行作为关联数组，或数字数组，或二者兼有
        } else {
            return '';
        }
    }

    //将查询结果输出一个数组并返回有多条记录的二维数组
    public function getRowsArray()
    {
        $this->query_result();
        if (mysqli_connect_errno() == '') {
            while ($rows = $this->con->fetch_assoc($this->resultDb, MYSQL_ASSOC)) {//mysql_fetch_array — 从结果集中取得一行作为关联数组，或数字数组，或二者兼有
                return $rowsArray[] = $rows;
            }
            return $this->rowsArray;
        } else {
            return '';
        }
    }

    //更新，插入，删除记录并获得受影响的记录数，进而判断操作是否成功
    public function uidRst()
    {
        if ($this->con == '') {
            $this->con = $this->con_init();
        }
        $this->rowsNum = $this->con->fetch_assoc();//以一个关联数组方式抓取一行结果。
        if (mysqli_connect_errno() == '') {
            return $this->rowsNum;
        } else {
            return $this->msg = mysqli_connect_error();//mysqli_connect_error() 返回连接错误信息
        }
    }

    //关闭数据库
    public function closeDatabase()
    {
        $this->query_result();
        mysqli_free_result($this->resultDb);
        $this->con->close();
    }
}







